package com.swordget.service.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.monitorjbl.xlsx.StreamingReader;
import com.swordget.commons.Constant;
import com.swordget.commons.ExecutorUtil;
import com.swordget.commons.FileHandlerUtil;
import com.swordget.conf.NamedThreadFactory;
import com.swordget.conf.UserProperties;
import com.swordget.dao.DataHandlerDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.TableDesign;
import com.swordget.entity.bo.ImportResolveBo;
import com.swordget.entity.bo.ResovleBo;
import com.swordget.entity.po.DataVerifyPo;
import com.swordget.entity.vo.*;
import com.swordget.exception.DataCheckException;
import com.swordget.exception.ResolveException;
import com.swordget.exception.VerifyFailException;
import com.swordget.runnable.InsertRunnable;
import com.swordget.service.*;
import com.swordget.util.EnvUitl;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.sql.Connection;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;

import static com.swordget.commons.ColumnType.NUMBER;
import static com.swordget.commons.PatternUtil.NUMBER_CHECK;
import static com.swordget.commons.SimpleFinalUtil.*;

/**
 * @Author: David
 * @Date: Created in 14:42 2020/4/15
 * @Description:
 * @Modified By:
 * @Version:
 */
@Service
public class ImportExportServiceImpl implements ImportExportService {

    private TableDefinitionService tableDefinitionService;

    private DataHandlerService dataHandlerService;

    private HandlerLogService handlerLogService;

    private UserProperties userProperties;

    private FileHandlerUtil fileHandlerUtil;

    private DataVerifyService dataVerifyService;

    private DruidDataSource dataSource;

    private TemplateManagerService templateManagerService;

    private LogService logService;

    private DataHandlerDao dataQueryDao;

    private SwitchingDataSourcesService switchingDataSourcesService;

    private TableManagerService tableManagerService;

    private final static int ARRAY_MAX = 10000;

    private final static Logger LOG = LoggerFactory.getLogger(ImportExportService.class);

    @Autowired
    public ImportExportServiceImpl(TableDefinitionService tableDefinitionService, DataHandlerService dataHandlerService,
                                   HandlerLogService handlerLogService, UserProperties userProperties,
                                   FileHandlerUtil fileHandlerUtil, DataVerifyService dataVerifyService,
                                   DruidDataSource dataSource, TemplateManagerService templateManagerService,
                                   LogService logService, DataHandlerDao dataQueryDao,
                                   SwitchingDataSourcesService switchingDataSourcesService,
                                   TableManagerService tableManagerService) {
        this.tableDefinitionService = tableDefinitionService;
        this.dataHandlerService = dataHandlerService;
        this.handlerLogService = handlerLogService;
        this.userProperties = userProperties;
        this.fileHandlerUtil = fileHandlerUtil;
        this.dataVerifyService = dataVerifyService;
        this.dataSource = dataSource;
        this.templateManagerService = templateManagerService;
        this.logService = logService;
        this.dataQueryDao = dataQueryDao;
        this.switchingDataSourcesService = switchingDataSourcesService;
        this.tableManagerService = tableManagerService;
    }

    @Override
    public String exportExcel(TableDesign tableDesign, JSONArray queryParam, String[] serials, ExcelVo excelVo, String loginId, String dataStatus) {
        final String tableId = tableDesign.getTableId();
        //校验并获取表名
        String tableName = tableDefinitionService.getTableName(loginId, tableId);

        TableDesign design = tableManagerService.flinOne(tableId);

        String dataSourceId = "";
        if (Objects.nonNull(design)) {
            dataSourceId = design.getDataSourceId();
        }

        //获取列集合
        final List<String> columns = tableDefinitionService.getColumns(tableId, false, false);
        final List<String> types = tableDefinitionService.getColumnTypes(tableId, false, false);
        final List<String> commons = tableDefinitionService.getCommons(tableId, false, false);
        final List<ColumnDesign> columnDesigns = tableDefinitionService.getColumnDesigns(tableId, false, false);


        //校验下请求参数
        boolean checkExcelVo = this.checkParams(excelVo, commons);
        //校验查询参数
        dataHandlerService.checkQueryParams(queryParam, columns);
        //添加状态参数
        queryParam = dataHandlerService.addDataStatus(queryParam, dataStatus);
        /*  tableName = PASS.equals(dataStatus) ? tableName : tableName + TMP_SUFFIX;*/
        final boolean singleSheet = excelVo.isSingleSheet();
        List<SheetVo> sheets = excelVo.getSheets();


        //查询导出的总数
        int count = dataHandlerService.getTotal(tableName, queryParam, serials, dataSourceId);

        String export_pageSize = EnvUitl.props.getProperty(Constant.export_pageSize);
        // 获取用户设置的单sheet存放条数
        int rows = StringUtils.isNotBlank(export_pageSize) ? Integer.parseInt(export_pageSize) : 60000;
        rows = rows > Constant.max_pageSize ? Constant.max_pageSize : rows;
        int page = (count % rows) > 0 ? count / rows + 1 : count / rows;

        SheetVo sheetVo = sheets.get(0);
        //用户是否要求按默认顺序导出excel
        List<String> exportColumns = columns;
        //如果用户要求包含头信息，那么头信息也要一起导出
        List<String> heads = commons;
        if (!sheetVo.isDefaultOrder()) {
            exportColumns = handlerHead(columns, commons, sheetVo.getHeads());
            heads = sheetVo.getHeads();
        }

        String fileName = tableName + XLSX_SUFFIX;
        File exportFile = new File(userProperties.getExportFilePath());
        if (!exportFile.exists() || exportFile.isFile()) {
            exportFile.mkdirs();
        }
        File writeFile = new File(exportFile.getAbsolutePath() + File.separator + fileName);
        try (Workbook workbook = new SXSSFWorkbook(10000);
             FileOutputStream outputStream = new FileOutputStream(writeFile)) {
            Sheet sheet = workbook.createSheet("sheet1");
            int rowNum = 0;
            if (sheetVo.isContainHead()) {
                //创建表头
                Row row = sheet.createRow(0);
                for (int j = 0; j < heads.size(); j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(heads.get(j));
                }
                rowNum = 1;
            }
            //TODO: 循环优化
            for (int i = 1; i <= page; i++) {
                if (i > 1) {
                    sheet = workbook.createSheet("sheet" + i);
                    rowNum = 0;
                    if (sheetVo.isContainHead()) {
                        //创建表头
                        Row row = sheet.createRow(0);
                        for (int j = 0; j < heads.size(); j++) {
                            Cell cell = row.createCell(j);
                            cell.setCellValue(heads.get(j));
                        }
                        rowNum = 1;
                    }
                }
                List<List<String>> dataList = dataHandlerService.queryDataByStatus(rows, i, tableName, queryParam,
                        exportColumns, types, columnDesigns, serials, dataSourceId);
                for (int k = 0; k < dataList.size(); k++) {
                    Row row = sheet.createRow(rowNum++);
                    List<String> objects = dataList.get(k);
                    for (int h = 0; h < objects.size(); h++) {
                        Cell cell = row.createCell(h);
                        cell.setCellValue(objects.get(h));
                    }
                }
            }
            workbook.write(outputStream);
        } catch (IOException e) {
            LOG.error("excel 文件写入失败!", e);
            throw new RuntimeException("excel 文件写入失败!");
        }

        //写入文件日志
        return handlerLogService.addUploadLog(writeFile, loginId);
    }

    @Override
    public String exportCsv(TableDesign tableDesign, JSONArray queryParam, String[] serials, CsvOrTxtVo csvVo, String loginId, String dataStatus) {
        return this.exportCsv(tableDesign, queryParam, serials, csvVo, COMMA_DELIMITER, CSV_SUFFIX, loginId, dataStatus);
    }

    @Override
    public String exportTxt(TableDesign tableDesign, JSONArray queryParam, String[] serials, CsvOrTxtVo csvVo, String loginId, String dataStatus) {
        Character delimiter = (char) csvVo.getSplit();

        if (delimiter == null) {
            throw new VerifyFailException("导出TXT文件请指定分割符");
        }
        List<Character> delimiterArray = Arrays.asList(new Character[]{'#', ',', '~', '*', '-', '|', '^'});
        if (!delimiterArray.contains(delimiter)) {
            throw new VerifyFailException("不正确的分割符");
        }
        return this.exportCsv(tableDesign, queryParam, serials, csvVo, delimiter, TXT_SUFFIX, loginId, dataStatus);
    }

    @Override
    public List<SheetVo> resolveExcel(File file, String loginId) {
        //读取文件
        try (Workbook workbook = StreamingReader
                .builder()
                .rowCacheSize(100)
                .bufferSize(4096)
                .open(file)) {
            //解析sheet 查看有多少个sheet
            List<SheetVo> sheetVos = new ArrayList<>(workbook.getNumberOfSheets());
            Iterator<Sheet> iterator = workbook.iterator();
            while (iterator.hasNext()) {
                Sheet sheet = iterator.next();
                Iterator<Row> rowIterator = sheet.rowIterator();
                List<String> heads = null;
                if (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    int cellTotal = row.getLastCellNum();
                    heads = new ArrayList<>(cellTotal);
                    for (int i = 0; i < cellTotal; i++) {
                        Cell cell = row.getCell(i);
                        heads.add(cell.getStringCellValue());
                    }
                }
                SheetVo responseVo = SheetVo.builder()
                        .sheetName(sheet.getSheetName())
                        .heads(heads)
                        .build();
                sheetVos.add(responseVo);
            }
            return sheetVos;
        } catch (Exception e) {
            throw new ResolveException("解析excel信息失败!", e);
        }
    }


    @Override
    public List<SheetVo> resolveExcelHead(ResovleHeadVo resovleHeadVo) throws IOException {
        try (Workbook workbook = StreamingReader
                .builder()
                .rowCacheSize(100)
                .bufferSize(4096)
                .open(getUploadFile(resovleHeadVo.getFileId()));) {
            List<String> sheets = resovleHeadVo.getSheets();
            Integer headRow = resovleHeadVo.getHeadRow();
            Integer lastRow = resovleHeadVo.getLastRow();
            Integer firstRow = resovleHeadVo.getFirstRow();
            List<SheetVo> sheetVos = new ArrayList<>();

            for (String sheetName : sheets) {
                Sheet sheet = workbook.getSheet(sheetName);
                int rowNum = 0;
                Iterator<Row> rowIterator = sheet.iterator();
                SheetVo sheetVo = null;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    if (++rowNum == headRow) {
                        int lastCell = row.getLastCellNum();
                        List<String> heads = new ArrayList<>();
                        for (int i = 0; i < lastCell; i++) {
                            String cellValue = row.getCell(i).getStringCellValue();
                            if (StringUtils.isNotBlank(cellValue)) {
                                heads.add(cellValue);
                            }
                        }
                        if (heads.size() <= 0) {
                            throw new ResolveException("不能指定空行!!!");
                        }
                        sheetVo = SheetVo.builder()
                                .heads(heads)
                                .startRow(firstRow)
                                .endRow(lastRow)
                                .sheetName(sheetName)
                                .headRow(headRow)
                                .build();
                        break;
                    }
                }
                sheetVos.add(sheetVo);
            }
            return sheetVos;
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public CsvOrTxtVo resolveTxtHead(ResovleHeadVo resovleHeadVo) throws IOException {
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(
                getUploadFile(resovleHeadVo.getFileId())), "GBK"))) {
            String line = null;
            int lineNum = 1;
            Integer headRow = resovleHeadVo.getHeadRow();
            Integer lastRow = resovleHeadVo.getLastRow();
            Integer firstRow = resovleHeadVo.getFirstRow();
            Map<String, String> map = new HashMap<>();
            map.put("*", "\\*");
            map.put("#", "\\#");
            map.put("~", "\\~");
            map.put("-", "\\-");
            map.put("|", "\\|");
            map.put("^", "\\^");
            map.put(",", "\\,");
            String spilt = map.get(resovleHeadVo.getSpilt());
            List<String> head = new ArrayList<>();
            while ((line = reader.readLine()) != null) {
                if (lineNum++ == headRow) {
                    head = Arrays.asList(line.split(spilt));
                    //TODO：生成后判断下
                    break;
                }
            }
            return CsvOrTxtVo.builder()
                    .headRow(headRow)
                    .heads(head)
                    .firstRow(firstRow)
                    .lastRow(lastRow)
                    .build();

        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void importFile(TableDesign tableDesign, List<CsvOrTxtImportVo> result, String fileId, String handleType, String loginId, String orgId) {
        long start = System.currentTimeMillis();
        LOG.info("========================== 用户提交了导入操作,开始进行数据导入 ===============================");
        //解析Bo
        ImportResolveBo testBo = this.resovleFoBo(tableDesign, result, fileId, loginId);
        LOG.info("==================================== 基础信息解析完成 =======================================");
        if (EXCEL.equals(handleType)) {
            //解析excel 列索引信息
            this.resolveCellIndexForExcel(testBo);
            LOG.info("================================ 文件索引信息解析完成 =======================================");

            //对数据进行基础校验
            this.baseVerifyForExcel(testBo, loginId);
            LOG.info("================================ 文件基础性校验完成 ==========================================");

            //解析文件插入临时表,如果插入数据过程中出现异常会回滚
            boolean isAppend = this.importForExcel(testBo, loginId, orgId, "");
            if (!isAppend) {
                throw new ResolveException("解析文件失败");
            }
            LOG.info("================================ 文件导入临时表完成 =============================================");
        } else {
            //解析excel 列索引信息
            this.resolveCellIndexForTxt(testBo);
            LOG.info("================================ 文件索引信息解析完成 =======================================");

            //对数据进行基础校验
            this.baseVerifyForTxt(testBo, loginId);
            LOG.info("================================ 文件基础性校验完成 ==========================================");

            //解析文件插入临时表,如果插入数据过程中出现异常会回滚
            boolean isAppend = this.importForTxt(testBo, loginId, orgId, "");
            if (!isAppend) {
                throw new ResolveException("解析文件失败");
            }
            LOG.info("================================ 文件导入临时表完成 =============================================");
        }

        //进行数据库校验
        try {
            boolean verify = this.dataVerify(testBo);
            if (!verify) {
                throw new ResolveException("数据库校验失败");
            }
            LOG.info("================================ 文件数据库校验完成 ===============================================");

            //写入审核日志
            //boolean addLog = dataHandlerService.addCheckLog(testBo);
            //日志写入失败，清空临时表
            /*if (!addLog) {
                throw new ResolveException("无法写入导入日志");
            }*/
            LOG.info("================================ 写入审核日志完成 ==================================================");

        } catch (Exception e) {
            //这两不可能导致状态不一致，如果出现异常则删除临时表数据
            dataHandlerService.removeByImportId(testBo.getTableNameTmp(), testBo.getImportId());
            throw e;
        }
        LOG.info("================================ 文件导入结束,用时 " + (System.currentTimeMillis() - start) + " ==================================================");
    }


    @Override
    public void resolveFile(String template, String tableId, String fileId, String loginId, String orgId, String dataSourceId) {
        long start = System.currentTimeMillis();
        LOG.info("========================== 用户提交了导入操作,开始进行数据导入 ===============================");
        //获取导入模板
        String templateJson = templateManagerService.findTemplateJson(template);
        //序列化
        SimpleRequestVo<TemplateVo> requestVo = (SimpleRequestVo<TemplateVo>) JSONObject.parseObject(templateJson,
                new TypeReference<SimpleRequestVo<TemplateVo>>() {
                });

        //解析Bo
        ImportResolveBo testBo = this.resovleFoBo(requestVo, tableId, fileId, loginId);
        String handlerType = testBo.getHandlerType();
        LOG.info("==================================== 基础信息解析完成 =======================================");
        if (EXCEL.equals(handlerType)) {
            //解析excel 列索引信息
            this.resolveCellIndexForExcel(testBo);
            LOG.info("================================ 文件索引信息解析完成 =======================================");

            //对数据进行基础校验
            this.baseVerifyForExcel(testBo, loginId);
            LOG.info("================================ 文件基础性校验完成 ==========================================");

            //解析文件插入临时表,如果插入数据过程中出现异常会回滚
            boolean isAppend = this.importForExcel(testBo, loginId, orgId, dataSourceId);
            if (!isAppend) {
                throw new ResolveException("解析文件失败");
            }
            LOG.info("================================ 文件导入临时表完成 =============================================");
        } else {
            //解析excel 列索引信息
            this.resolveCellIndexForTxt(testBo);
            LOG.info("================================ 文件索引信息解析完成 =======================================");

            //对数据进行基础校验
            this.baseVerifyForTxt(testBo, loginId);
            LOG.info("================================ 文件基础性校验完成 ==========================================");

            //解析文件插入临时表,如果插入数据过程中出现异常会回滚
            boolean isAppend = this.importForTxt(testBo, loginId, orgId, dataSourceId);
            if (!isAppend) {
                throw new ResolveException("解析文件失败");
            }
            LOG.info("================================ 文件导入临时表完成 =============================================");
        }

        //进行数据库校验
        try {
            boolean verify = this.dataVerify(testBo);
            if (!verify) {
                throw new ResolveException("数据库校验失败");
            }
            LOG.info("================================ 文件数据库校验完成 ===============================================");

            //写入审核日志
            boolean addLog = dataHandlerService.addCheckLog(testBo);
            //日志写入失败，清空临时表
            if (!addLog) {
                throw new ResolveException("无法写入导入日志");
            }
            LOG.info("================================ 写入审核日志完成 ==================================================");

        } catch (Exception e) {
            //这两不可能导致状态不一致，如果出现异常则删除临时表数据
            dataHandlerService.removeByImportId(testBo.getTableNameTmp(), testBo.getImportId());
            throw e;
        }
        LOG.info("================================ 文件导入结束,用时 " + (System.currentTimeMillis() - start) + " ==================================================");
    }

    public void resolveCellIndexForExcel(ImportResolveBo testBo) {
        try (Workbook workbook = StreamingReader
                .builder()
                .rowCacheSize(100)
                .bufferSize(4096)
                .open(testBo.getUploadFile());) {
            List<ResovleBo> resovleBos = testBo.getResults();
            ResovleBo resovleBo = testBo.getResult();
            Iterator<Sheet> sheetIterator = workbook.iterator();
            List<String> titles = resovleBo.getTitles();
            List<String> heads = new ArrayList<>();
            List<String> sheet1 = new ArrayList<>();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                Iterator<Row> rowIterator = sheet.iterator();
                int rowNum = 0;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    if (++rowNum == resovleBo.getHeadRow()) {
                        List<String> heads1 = new ArrayList<>();
                        int lastCell = row.getLastCellNum();
                        for (int i = 0; i < lastCell; i++) {
                            Cell cell = row.getCell(i);
                            String value = "";
                            if (cell != null) {
                                value = cell.getStringCellValue();
                            }
                            heads1.add(value);
                        }
                        if (heads1.equals(titles)) {
                            sheet1.add(sheet.getSheetName());
                            if (heads.size() == 0) {
                                heads.addAll(heads1);
                            }
                        }
                        break;
                    }
                    break;
                }
                //只解析excel第一个sheet表
                // break;
            }
            testBo.setSheet(sheet1);
            List<Integer> cellIndex = this.getIndex(titles, heads);
            resovleBo.setCellIndex(cellIndex);
        } catch (VerifyFailException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("解析excel 头信息过程中出现异常!", e);
        }
    }

    public void resolveCellIndexForTxt(ImportResolveBo testBo) {
        //List<ResovleBo> resovleBos = testBo.getResults();
        //txt 默认只解析第一个ResovleBo
        ResovleBo resovleBo = testBo.getResult();
        List<String> titles = resovleBo.getTitles();
        List<String> heads = new ArrayList<>();
        String charset = resovleBo.getCharset();
        int headRow = resovleBo.getHeadRow();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(testBo.getUploadFile()), charset))) {
            String line = null;
            int rowNum = 0;
            while ((line = reader.readLine()) != null) {
                if (++rowNum == headRow) {
                    heads = Arrays.asList(line.split(resovleBo.getSplit()));
                    break;
                }
            }
            List<Integer> cellIndex = this.getIndex(titles, heads);
            //设置索引列
            resovleBo.setCellIndex(cellIndex);
        } catch (VerifyFailException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException("解析标题信息过程中出现异常!", e);
        }
    }

    /**
     * 根据模板定义的标题列,找出文件中标题对应的索引
     *
     * @param titles 模板定义标题列
     * @param heads  文件中的标题列
     * @return
     */
    private List<Integer> getIndex(List<String> titles, List<String> heads) {
        //标题列找重，不关心其他列重不重复只关注，模板中指定的
        long count = heads.stream()
                .filter(c -> titles.contains(c))
                .count();
        if (count != titles.size()) {
            throw new VerifyFailException("您指定的标题列缺少字段或存在重复,请改正后重试!");
        }

        //取出列的INDEX
        List<String> finalHeads = heads;
        List<Integer> cellIndex = titles.stream()
                .map(t -> finalHeads.indexOf(t))
                .filter(t -> t != -1)
                .collect(Collectors.toList());

        if (titles.size() != cellIndex.size()) {
            throw new VerifyFailException("无法解析您指定的标题列,请确认指定正确!");
        }
        return cellIndex;
    }

    public ImportResolveBo resovleFoBo(TableDesign tableDesign, List<CsvOrTxtImportVo> result, String fileId, String loginId) {

        //解析表名，获取文件
        String tableId = tableDesign.getTableId();

        //获取表名
        String tableName = tableDefinitionService.getTableName(loginId, tableId);

        //查找文件所在位置
        String filePath = handlerLogService.findUploadFilePath(fileId);
        if (StringUtils.isBlank(filePath)) {
            throw new ResolveException("无法找到解析文件,请您重新上传!");
        }

        //获取上传文件
        File uploadFile = new File(filePath);
        if (!uploadFile.exists() || uploadFile.isDirectory()) {
            throw new ResolveException("无法找到解析文件,请您重新上传!");
        }

        //获取表结构
        List<ColumnDesign> columnDesigns = tableDefinitionService.getColumnDesigns(tableId, false, false);

        //标识导入id
        String importId = String.format("%020d", Math.round(Math.random() * 1000000));

        List<ResovleBo> resovleBos = new ArrayList<>();
        for (CsvOrTxtImportVo csvOrTxtImportVo : result) {

            //判断用户要解析的列是否有重复
            List<ResourceVo> resourceVos = csvOrTxtImportVo.getColumnMap();
            //过滤掉未选择的列和空列，也就是不用导入的列
            resourceVos = resourceVos.stream()
                    .filter(r -> StringUtils.isNotBlank(r.getColumn()))
                    .filter(ResourceVo::isChecked)
                    .collect(Collectors.toList());
            //没有指定任何导入列
            if (resourceVos.size() <= 0) {
                throw new VerifyFailException("您没有指定任何导入列,请重试!");
            }

            //对导入列进行去重
            long resolverCount = resourceVos.stream()
                    .filter(ResourceVo::isChecked)
                    .map(ResourceVo::getColumn)
                    .distinct()
                    .count();
            if (resourceVos.size() != resolverCount) {
                throw new VerifyFailException("您选择要导入的列中存在重复,请重试!");
            }

            //分别解析要导入的标题和要导入的字段名
            List<String> titles = resourceVos.stream()
                    .map(ResourceVo::getResource)
                    .collect(Collectors.toList());
            List<String> columns = resourceVos.stream()
                    .map(ResourceVo::getColumn)
                    .collect(Collectors.toList());

            //判断用户是否指定了必填列
            columnDesigns.stream()
                    .filter(c -> YES.equals(c.getNotNull()))
                    .forEach(c -> {
                        if (!columns.contains(c.getName())) {
                            throw new VerifyFailException("你指定的导入列中必须包含必填列,请重试!");
                        }
                    });

            //根据导出列的顺序组织 columnDesigns 对象便于后面校验
            List<ColumnDesign> impColumDesigns = new ArrayList<>(columns.size());

            for (String column : columns) {
                boolean flag = true;
                for (ColumnDesign columnDesign : columnDesigns) {
                    if (column.equals(columnDesign.getName())) {
                        impColumDesigns.add(columnDesign);
                        flag = false;
                        break;
                    }
                }
                if (flag) {
                    throw new VerifyFailException("您指定的列中包含未知列,请检查并重试");
                }
            }


            //解析结果保存到BO对象
            resovleBos.add(ResovleBo.builder()
                    .titles(titles)
                    .columns(columns)
                    .columnDesigns(impColumDesigns)
                    .headRow(csvOrTxtImportVo.getHeadRow())
                    .firstRow(csvOrTxtImportVo.getFirstRow())
                    .lastRow(csvOrTxtImportVo.getLastRow())
                    .sheetName(csvOrTxtImportVo.getSheetName())
                    .split(csvOrTxtImportVo.getSplit())
                    .charset(csvOrTxtImportVo.getCharset())
                    .build());

        }

        //组装BO对象
        return ImportResolveBo.builder()
                .tableId(tableId)
                .tableName(tableName)
                .tableNameTmp(tableName)
                .uploadFile(uploadFile)
                .importId(importId)
                .results(resovleBos)
                .loginId(loginId)
                .build();
    }

    public ImportResolveBo resovleFoBo(SimpleRequestVo<TemplateVo> requestVo, String tableId, String fileId, String loginId) {

        //表定义
        final TableDesign tableDesign = requestVo.getData().getTable();

        //验证模板是否是此表的
        if (StringUtils.isBlank(tableId) || !tableId.equals(tableDesign.getTableId())) {
            throw new VerifyFailException("检查您的请求参数，输入参数不正确!");
        }

        //获取表名
        String tableName = tableDefinitionService.getTableName(loginId, tableId);

        //查找文件所在位置
        String filePath = handlerLogService.findUploadFilePath(fileId);
        if (StringUtils.isBlank(filePath)) {
            throw new ResolveException("无法找到解析文件,请您重新上传!");
        }

        //获取上传文件
        File uploadFile = new File(filePath);
        if (!uploadFile.exists() || uploadFile.isDirectory()) {
            throw new ResolveException("无法找到解析文件,请您重新上传!");
        }

        //解析文件导入类型
        String fileName = uploadFile.getName();
        String fileType = fileName.substring(fileName.lastIndexOf('.'), fileName.length());
        if (".xls".equals(fileType) || ".xlsx".equals(fileType)) {
            fileType = EXCEL;
        } else if (".csv".equals(fileType)) {
            fileType = CSV;
        } else if (".txt".equals(fileType)) {
            fileType = TXT;
        } else {
            throw new VerifyFailException("不支持的文件类型!");
        }


        //获取表结构
        List<ColumnDesign> columnDesigns = tableDefinitionService.getColumnDesigns(tableId, false, false);

        //标识导入id
        String importId = String.format("%020d", Math.round(Math.random() * 1000000));

        final CsvOrTxtImportVo result = requestVo.getData().getResult();
        final List<ResourceVo> resourceVos = result.getColumnMap();

        //分别解析要导入的标题和要导入的字段名
        List<String> titles = resourceVos.stream()
                .map(ResourceVo::getResource)
                .collect(Collectors.toList());

        List<String> columns = resourceVos.stream()
                .map(ResourceVo::getColumn)
                .collect(Collectors.toList());

        //根据导出列的顺序组织 columnDesigns 对象便于后面校验
        List<ColumnDesign> impColumDesigns = new ArrayList<>(columns.size());

        for (String column : columns) {
            for (ColumnDesign columnDesign : columnDesigns) {
                if (column.equals(columnDesign.getName())) {
                    impColumDesigns.add(columnDesign);
                    break;
                }
            }
        }
        Map<String, String> map = new HashMap<>();
        map.put("*", "\\*");
        map.put("#", "\\#");
        map.put("~", "\\~");
        map.put("-", "\\-");
        map.put("|", "\\|");
        map.put("^", "\\^");
        map.put(",", "\\,");
        //解析结果保存到BO对象
        ResovleBo resovleBo = ResovleBo.builder()
                .titles(titles)
                .columns(columns)
                .columnDesigns(impColumDesigns)
                .headRow(result.getHeadRow())
                .firstRow(result.getFirstRow())
                .lastRow(result.getLastRow())
                .sheetName(result.getSheetName())
                .split(map.get(result.getSplit()))
                .charset(result.getCharset())
                .build();


        //组装BO对象
        return ImportResolveBo.builder()
                .tableId(tableId)
                .tableName(tableName)
                .tableNameTmp(tableName)
                .uploadFile(uploadFile)
                .importId(importId)
                .result(resovleBo)
                .loginId(loginId)
                .handlerType(fileType)
                .build();
    }


    private boolean importForExcel(ImportResolveBo testBo, String loginId, String orgId, String dataSourceId) {
        List<Future<Boolean>> futures = new ArrayList<>();
        ThreadPoolExecutor executor = new ThreadPoolExecutor(8, 10,
                10000, TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(500),
                new NamedThreadFactory("ExcelImport-ExecutorService"));

        final String apo = "'";
        //记录导入条数
        long importLines = 0;
        Row row = null;
        Cell cell = null;
        try (Connection connection = switchingDataSourcesService.switchingDataSources(dataSourceId);) {
            connection.setAutoCommit(false);
            try (Workbook workbook = StreamingReader
                    .builder()
                    .rowCacheSize(1000)
                    .bufferSize(4096)
                    .open(testBo.getUploadFile());) {

                //List<ResovleBo> resovleBos = testBo.getResults();
                String importId = testBo.getImportId();

                ResovleBo resovleBo = testBo.getResult();
                //要导入的列字段
                List<String> columns = resovleBo.getColumns();
                List<ColumnDesign> columnDesigns = resovleBo.getColumnDesigns();

                //导入列的索引
                List<Integer> cellIndex = resovleBo.getCellIndex();
                int headRow = resovleBo.getHeadRow();
                int firstRow = resovleBo.getFirstRow();
                Integer lastRow = resovleBo.getLastRow();

                Iterator<Sheet> sheetIterator = workbook.iterator();
                while (sheetIterator.hasNext()) {
                    Sheet sheet = sheetIterator.next();
                    Iterator<Row> rowIterator = sheet.iterator();
                    int cellSize = columns.size();
                    //记录解析数据
                    String[][] dataArray = new String[ARRAY_MAX][cellSize];
                    int rowNum = 0, indexNum = 0;
                    if (testBo.getSheet().contains(sheet.getSheetName())) {
                        while (rowIterator.hasNext()) {
                            row = rowIterator.next();
                            rowNum++;
                            if (rowNum == headRow || firstRow > rowNum) {
                                continue;
                            }
                            if (lastRow != null && lastRow < rowNum) {
                                break;
                            }
                            for (int i = 0; i < cellIndex.size(); i++) {
                                int index = cellIndex.get(i);
                                cell = row.getCell(index);
                                ColumnDesign columnDesign = columnDesigns.get(i);
                                String value = "";
                                if (cell != null) {
                                    value = cell.getStringCellValue();
                                }
                                if (StringUtils.isNotBlank(value)) {
                                    String newValue = value;
                                    if (value.indexOf("'") != -1) {
                                        newValue = value.replaceAll("'", "''");
                                    }

                                    if ("NUMBER".equals(columnDesign.getType())) {
                                        newValue = newValue.replaceAll(",", "");
                                    }
                                    dataArray[indexNum][i] = apo + newValue + apo;
                                } else {
                                    dataArray[indexNum][i] = null;
                                }
                            }

                            if (++indexNum >= ARRAY_MAX) {
                                futures.add(executor.submit(new InsertRunnable(connection, testBo.getTableNameTmp(), dataArray, columns, indexNum, loginId, importId)));
                                dataArray = new String[ARRAY_MAX][cellSize];
                                importLines += indexNum;
                                indexNum = 0;
                            }
                        }
                    }

                    futures.add(executor.submit(new InsertRunnable(connection, testBo.getTableNameTmp(), dataArray, columns, indexNum, loginId, importId)));
                    importLines += indexNum;
                    //只解析excel第一个sheet表
                    //break;
                }

            } catch (Exception e) {
                connection.rollback();
                throw new RuntimeException("解析excel数据并插入数据是发生异常", e);
            }

            for (Future<Boolean> future : futures) {
                try {
                    //如果有一个future返回失败那么取消所有还未执行完毕的任务
                    if (!future.get(5, TimeUnit.HOURS)) {
                        for (Future<Boolean> subFuture : futures) {
                            if (!subFuture.isDone() || !subFuture.isCancelled()) {
                                subFuture.cancel(true);
                            }
                        }
                        connection.rollback();
                        throw new RuntimeException("任务执行过程中失败!");
                    }
                } catch (InterruptedException e) {
                    Thread.currentThread().interrupt();
                    connection.rollback();
                    throw e;
                } catch (ExecutionException e) {
                    connection.rollback();
                    throw e;
                } catch (TimeoutException e) {
                    connection.rollback();
                    LOG.error("导入excel时获取任务执行结果超时");
                    throw e;
                }
            }
            connection.commit();
            //记录导入日志
            logService.addLog(loginId, "导入表：" + testBo.getTableNameTmp() + "导入时间："
                    + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + "导入笔数：" + importLines, "导入EXCEL", orgId);
            return true;
        } catch (Exception e) {
            LOG.error("解析excel 并插入数据库过程中出现异常", e);
            ExecutorUtil.shutdownNow(executor, 10000);
        } finally {
            ExecutorUtil.gracefulShutdown(executor, 10000);
        }
        return false;
    }

    private boolean importForTxt(ImportResolveBo testBo, String loginId, String orgId, String dataSourceId) {
        List<Future<Boolean>> futures = new ArrayList<>();
        ThreadPoolExecutor executor = new ThreadPoolExecutor(8, 10,
                10000, TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(10),
                new NamedThreadFactory("CsvImport-ExecutorService"));

        final String apo = "'";
        try (Connection connection = switchingDataSourcesService.switchingDataSources(dataSourceId);) {
            connection.setAutoCommit(false);
            //List<ResovleBo> resovleBos = testBo.getResults();
            ResovleBo resovleBo = testBo.getResult();

            String importId = testBo.getImportId();
            String charset = resovleBo.getCharset();
            //要导入的列字段
            List<String> columns = resovleBo.getColumns();
            //导入列的索引
            List<Integer> cellIndex = resovleBo.getCellIndex();

            List<ColumnDesign> columnDesigns = resovleBo.getColumnDesigns();

            int cellSize = columns.size();
            int headRow = resovleBo.getHeadRow();
            int firstRow = resovleBo.getFirstRow();
            Integer lastRow = resovleBo.getLastRow();
            String split = resovleBo.getSplit();
            long importLines = 0;
            try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(testBo.getUploadFile()), charset))) {
                //记录解析数据
                String[][] dataArray = new String[ARRAY_MAX][cellSize];
                String line = null;
                int rowNum = 0, indexNum = 0;
                while ((line = reader.readLine()) != null) {
                    rowNum++;
                    if (rowNum == headRow || firstRow > rowNum) {
                        continue;
                    }
                    if (lastRow != null && lastRow < rowNum) {
                        break;
                    }
                    String[] lineArray = line.split(split, -1);
                    for (int i = 0; i < cellIndex.size(); i++) {
                        String value = lineArray[cellIndex.get(i)];
                        if (StringUtils.isNotBlank(value)) {
                            dataArray[indexNum][i] = apo + value + apo;
                        } else {
                            dataArray[indexNum][i] = null;
                        }
                    }
                    //添加隐藏字段
                    if (++indexNum >= ARRAY_MAX) {
                        futures.add(executor.submit(new InsertRunnable(connection, testBo.getTableNameTmp(), dataArray, columns, indexNum, loginId, importId)));
                        dataArray = new String[ARRAY_MAX][cellSize];
                        importLines += indexNum;
                        indexNum = 0;
                    }
                }
                futures.add(executor.submit(new InsertRunnable(connection, testBo.getTableNameTmp(), dataArray, columns, indexNum, loginId, importId)));
                importLines += indexNum;
            } catch (Exception e) {
                connection.rollback();
                throw new RuntimeException("解析excel数据并插入数据是发生异常", e);
            }

            for (Future<Boolean> future : futures) {
                try {
                    //如果有一个future返回失败那么取消所有还未执行完毕的任务
                    if (!future.get(5, TimeUnit.HOURS)) {
                        for (Future<Boolean> subFuture : futures) {
                            if (!subFuture.isDone() || !subFuture.isCancelled()) {
                                subFuture.cancel(true);
                            }
                        }
                        connection.rollback();
                        throw new RuntimeException("任务执行过程中失败!");
                    }
                } catch (InterruptedException e) {
                    Thread.currentThread().interrupt();
                    connection.rollback();
                    throw e;
                } catch (ExecutionException e) {
                    connection.rollback();
                    throw e;
                } catch (TimeoutException e) {
                    connection.rollback();
                    LOG.error("导入excel时获取任务执行结果超时");
                    throw e;
                }
            }
            connection.commit();
            //记录导入日志
            logService.addLog(loginId, "导入表：" + testBo.getTableNameTmp() + "导入时间："
                    + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + "导入笔数：" + importLines, "导入TXT或CSV", orgId);
            return true;
        } catch (Exception e) {
            LOG.error("解析excel 并插入数据库过程中出现异常", e);
            ExecutorUtil.shutdownNow(executor, 10000);
        } finally {
            ExecutorUtil.gracefulShutdown(executor, 10000);
        }
        return false;
    }


    private void baseVerifyForExcel(ImportResolveBo testBo, String loginId) {
        File uploadFile = testBo.getUploadFile();
        List<ResovleBo> resovleBos = testBo.getResults();
        StringBuffer errorLog = new StringBuffer();
        try (Workbook workbook = StreamingReader
                .builder()
                .rowCacheSize(100)
                .bufferSize(4096)
                .open(uploadFile);) {
            Row row = null;
            Cell cell = null;
            ResovleBo resovleBo = testBo.getResult();

            Integer headRow = resovleBo.getHeadRow();
            Integer firstNum = resovleBo.getFirstRow();
            Integer lastNum = resovleBo.getLastRow();
            List<Integer> cellIndex = resovleBo.getCellIndex();
            List<ColumnDesign> columnDesigns = resovleBo.getColumnDesigns();
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                //重新获取迭代器
                Iterator<Row> rowIterator = sheet.iterator();
                int rowNum = 0, indexNum = 0, loopNum = 0;
                String[][] datas = new String[ARRAY_MAX][cellIndex.size()];
                if (testBo.getSheet().contains(sheet.getSheetName())) {
                    StringBuffer errorBuffer = new StringBuffer();
                    while (rowIterator.hasNext()) {
                        row = rowIterator.next();
                        rowNum++;
                        if (rowNum == headRow || rowNum < firstNum) {
                            continue;
                        }

                        if (lastNum != null && lastNum > rowNum) {
                            break;
                        }

                        for (int j = 0; j < cellIndex.size(); j++) {

                            cell = row.getCell(cellIndex.get(j));
                            String value = "";
                            if (cell != null) {
                                value = cell.getStringCellValue();
                            }
                            datas[indexNum][j] = value;
                        }
                        //避免内存溢出
                        if (++indexNum >= ARRAY_MAX) {
                            StringBuffer error = this.verifyBase(columnDesigns, datas, indexNum, loopNum++, testBo);
                            errorBuffer.append(error);
                            datas = new String[ARRAY_MAX][cellIndex.size()];
                            indexNum = 0;
                        }
                    }
                    StringBuffer error = this.verifyBase(columnDesigns, datas, indexNum, loopNum, testBo);
                    errorBuffer.append(error);
                    if (StringUtils.isNotBlank(errorBuffer)) {
                        errorLog.append("=====================" + sheet.getSheetName() + "=====================\n");
                        errorLog.append(errorBuffer);
                        errorLog.append("===========================================================\n");
                    }
                }
                //只解析excel第一个sheet表
                //break;
            }
        } catch (Exception e) {
            throw new ResolveException("对文件进行基础校验的时候发生异常!", e);
        }
        //判断是否发生校验错误
        if (StringUtils.isNotBlank(errorLog)) {
            this.writeErrorLog(errorLog, loginId);
        }
    }


    /**
     * 对txt文件和csv文件进行基础校验
     *
     * @param testBo
     * @param loginId
     */
    private void baseVerifyForTxt(ImportResolveBo testBo, String loginId) {
        StringBuffer checkMsg = new StringBuffer();
        //List<ResovleBo> resovleBos = testBo.getResults();
        ResovleBo resovleBo = testBo.getResult();
        Integer headRow = resovleBo.getHeadRow();
        Integer firstNum = resovleBo.getFirstRow();
        Integer lastNum = resovleBo.getLastRow();
        List<Integer> cellIndex = resovleBo.getCellIndex();
        List<ColumnDesign> columnDesigns = resovleBo.getColumnDesigns();
        String charset = resovleBo.getCharset();
        StringBuffer errorBuffer = new StringBuffer();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(testBo.getUploadFile()), charset))) {

            String line = null;
            String[][] datas = new String[ARRAY_MAX][cellIndex.size()];
            int rowNum = 0, indexNum = 0, loopNum = 0;
            while ((line = reader.readLine()) != null) {
                rowNum++;
                if (rowNum == headRow || rowNum < firstNum) {
                    continue;
                }

                if (lastNum != null && lastNum > rowNum) {
                    break;
                }

                String[] lineArray = line.split(resovleBo.getSplit());
                for (int j = 0; j < cellIndex.size(); j++) {
                    String value = lineArray[cellIndex.get(j)];
                    datas[indexNum][j] = value;
                }
                //避免内存溢出
                if (++indexNum >= ARRAY_MAX) {
                    StringBuffer error = this.verifyBase(columnDesigns, datas, indexNum, loopNum++, testBo);
                    errorBuffer.append(error);
                    datas = new String[ARRAY_MAX][cellIndex.size()];
                    indexNum = 0;
                }
            }
            StringBuffer error = this.verifyBase(columnDesigns, datas, indexNum, loopNum, testBo);
            errorBuffer.append(error);

        } catch (Exception e) {
            LOG.error("程序执行过程中出现异常", e);
            throw new ResolveException("对文件进行基础校验的时候发生异常!", e);
        }
        //判断是否发生校验错误
        if (StringUtils.isNotBlank(errorBuffer)) {
            errorBuffer.insert(0, "========================================== 对导入数据进行基础校验不通过 ================================\n");
            errorBuffer.append("================================================================================================\n");
            this.writeErrorLog(errorBuffer, loginId);
        }
    }

    private StringBuffer verifyBase(List<ColumnDesign> columnDesigns, String[][] datas, int offset, int loopNum, ImportResolveBo testBo) {
        StringBuffer errorBuffer = new StringBuffer();
        int baseNum = (loopNum * ARRAY_MAX);
        Map<String, Integer> repeats = new HashMap<>(); // 校验导入数据里是否有重复数据
        for (int j = 0; j < offset; j++) {
            String[] data = datas[j];
            for (int i = 0, rowNum = 1; i < columnDesigns.size(); i++, rowNum++) {
                String value = data[i];
                ColumnDesign columnDesign = columnDesigns.get(i);
                String name = columnDesign.getName();
                String comment = columnDesign.getComment();
                //参数不能为空校验
                if (YES.equals(columnDesign.getNotNull()) && NO.equals(columnDesign.getIsHidden()) && StringUtils.isBlank(value)) {
                    errorBuffer.append("行" + (baseNum + j + 1) + comment + "请检查必输项是否填写完整!\n");
                    continue;
                }
                if (YES.equals(columnDesign.getNotRepeat())) {
                    int n = dataQueryDao.getCount(testBo.getTableNameTmp(), name, value);
                    //  int n1=dataQueryDao.getCount(testBo.getTableNameTmp().replace(TMP_SUFFIX,""), name, value);
                    if (n > 0) {
                        errorBuffer.append("行" + (baseNum + j + 1) + comment + "不可重复!\n");
                        continue;
                    }

                    if (repeats.containsKey(value)) {
                        repeats.put(value, repeats.get(value) + 1);
                        errorBuffer.append("行" + (baseNum + j + 1) + comment + "不可重复!\n");
                        continue;
                    }
                    repeats.put(value, 0);
                }


                if (StringUtils.isNotBlank(value) && NO.equals(columnDesign.getIsHidden())) {
                    //字段长度校验
                    if (value.length() > columnDesign.getWidth() && !NUMBER.equals(columnDesign.getType())) {
                        errorBuffer.append("行" + (baseNum + j + 1) + comment + "字段长度不正确!\n");
                        continue;
                    }

                    //值类型校验如果是number不允许数字外的字符,number类型要校验小数位
                    if (NUMBER.equals(columnDesign.getType())) {
                        //小数点也占位
                        if (value.length() > columnDesign.getWidth() + 1) {
                            errorBuffer.append("行" + (baseNum + j + 1) + comment + "字段长度不正确!\n");
                            continue;
                        }

                        if (!NUMBER_CHECK.matcher(value).find()) {
                            errorBuffer.append("行" + (baseNum + j + 1) + comment + "字段必须为数值型\n");
                            continue;
                        }
                        int indexOf = value.lastIndexOf(".");
                        if (indexOf > -1 && value.length() - (indexOf + 1) > columnDesign.getPoint()) {
                            errorBuffer.append("行" + (baseNum + j + 1) + comment + "字段数值长度不正确\n");
                            continue;
                        }
                    }
                    //日期格式校验
                    if (DATE_BOX.equals(columnDesign.getContextType())) {
                        SimpleDateFormat sdf = new SimpleDateFormat(columnDesign.getDateFormat());
                        try {
                            sdf.setLenient(false);
                            sdf.parse(value);
                        } catch (ParseException e) {
                            errorBuffer.append("行" + (baseNum + j + 1) + comment + "无法解析日期,请确认输入的日期及其格式是否正确\n");
                        }
                    }

                }
            }
        }
        return errorBuffer;
    }


    private File getUploadFile(String fileId) {
        String uploadFile = handlerLogService.findUploadFilePath(fileId);
        File file = new File(uploadFile);
        if (!file.exists() || file.isDirectory()) {
            throw new VerifyFailException("没有找到上传文件,请重试!");
        }
        return file;
    }

    private boolean dataVerify(ImportResolveBo testBo) {
        LOG.info("开始对文件进行数据库校验");
        //临时表插入完成，调用校验服务校验刚刚导入的数据
        List<DataVerifyPo> verifyPos = dataVerifyService.verify(testBo.getTableId(), testBo.getTableName(), testBo.getTableNameTmp(), testBo.getImportId());
        //TODO:数据库校验失败,返回错误文件id,目前这里直接抛异常
        if (verifyPos == null || verifyPos.size() <= 0) {
            return true;
        }
        //校验不通过清空临时表导入数据
        boolean isRemove = dataHandlerService.removeByImportId(testBo.getTableNameTmp(), testBo.getImportId());
        //写入校验失败信息,返回给前端
        StringBuffer errorBuffer = new StringBuffer();
        errorBuffer.append("==================================== 数据校验出现错误 ====================================");
        for (DataVerifyPo verifyPo : verifyPos) {
            errorBuffer.append(verifyPo.getMark());
            errorBuffer.append("校验失败：");
            errorBuffer.append(verifyPo.getReason());
            errorBuffer.append("\r\n");
        }
        errorBuffer.append("=========================================================================================");
        //写入错误信息到文件
        this.writeErrorLog(errorBuffer, testBo.getLoginId());
        return false;
    }

    /**
     * 写入校验校验错误信息
     *
     * @param errorBuffer
     * @param loginId
     */
    private void writeErrorLog(StringBuffer errorBuffer, String loginId) {
        try {
            File errorFile = fileHandlerUtil.writerErrorFile(errorBuffer.toString());
            //添加日志
            String fileId = handlerLogService.addUploadLog(errorFile.getAbsoluteFile(), loginId);
            throw new DataCheckException(fileId);
        } catch (IOException e) {
            LOG.error("程序执行过程中出现异常", e);
            throw new ResolveException("对文件进行基础校验的时候发生异常!", e);
        }
    }


    private String exportCsv(TableDesign tableDesign, JSONArray queryParam, String[] serials, CsvOrTxtVo csvVo,
                             char delimiter, String fileSuffix, String loginId, String dataStatus) {
        final String tableId = tableDesign.getTableId();

        TableDesign design = tableManagerService.flinOne(tableId);

        String dataSourceId = "";
        if (Objects.nonNull(design)) {
            dataSourceId = design.getDataSourceId();
        }


        //校验并获取表名
        String tableName = tableDefinitionService.getTableName(loginId, tableId);
        //获取列集合
        final List<String> columns = tableDefinitionService.getColumns(tableId, false, false);
        final List<String> types = tableDefinitionService.getColumnTypes(tableId, false, false);
        final List<String> commons = tableDefinitionService.getCommons(tableId, false, false);
        final List<ColumnDesign> columnDesigns = tableDefinitionService.getColumnDesigns(tableId, false, false);

        //检查参数
        dataHandlerService.checkQueryParams(queryParam, columns);
        //添加状态
        queryParam = dataHandlerService.addDataStatus(queryParam, dataStatus);
        /* tableName = PASS.equals(dataStatus) ? tableName : tableName + TMP_SUFFIX;*/

        //组织校验要导出的列
        List<String> exportColumns = columns;
        List<String> heads = commons;
        if (!csvVo.isDefaultOrder()) {
            //exportColumns = csvVo.getHeads();
            heads = csvVo.getHeads();
            exportColumns = handlerHead(columns, commons, heads);
            if (exportColumns == null || exportColumns.size() <= 0) {
                throw new VerifyFailException("您选择非默认导出并未指定导出顺序,请先指定导出顺序!");
            }
            if (!commons.containsAll(heads)) {
                throw new VerifyFailException("请检查您的参数是否正确!");
            }
        }
        //如果用户要求包含头信息，那么头信息也要一起导出

       /* if (csvVo.isContainHead()) {
            heads = handlerHead(columns, commons, csvVo.getHeads());
        }*/
        //查询导出的数据量
        int count = dataHandlerService.getTotal(tableName, queryParam, serials, dataSourceId);
        int rows = 60000;
        int page = (count % rows) > 0 ? count / rows + 1 : count / rows;

        //初始化csvformat,注意必须是GBK编码
        CSVFormat formator = CSVFormat.DEFAULT
                .withDelimiter(delimiter);
        String fileName = tableName + fileSuffix;

        File exportPath = new File(userProperties.getExportFilePath());
        if (!exportPath.exists()) {
            exportPath.mkdirs();
        }
        File writeFile = new File(exportPath + File.separator + fileName);
        try (FileOutputStream fos = new FileOutputStream(writeFile);
             OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK");
             CSVPrinter printer = new CSVPrinter(osw, formator)) {
            if (csvVo.isContainHead()) {
                printer.printRecord(heads);
            }
            //开始导出
            for (int i = 1; i <= page; i++) {
                List<List<String>> dataList = dataHandlerService.queryDataByStatus(rows, i, tableName, queryParam,
                        exportColumns, types, columnDesigns, serials, dataSourceId);
                for (List<String> list : dataList) {
                    printer.printRecord(list);
                }
            }
        } catch (Exception e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return handlerLogService.addUploadLog(writeFile, loginId);
    }

    private List<String> handlerHead(List<String> columns, List<String> commons, List<String> heads) {
        List<String> newColumns = new ArrayList<>();
        for (int i = 0; i < heads.size(); i++) {
            String head = heads.get(i);
            newColumns.add(columns.get(commons.indexOf(head)));
        }
        return newColumns;
    }


    /**
     * 校验excelvo 请求是否正确
     *
     * @param excelVo
     * @param comments
     * @return
     */
    private boolean checkParams(ExcelVo excelVo, List<String> comments) {
        boolean singleSheet = excelVo.isSingleSheet();
        List<SheetVo> sheets = excelVo.getSheets();
        if (singleSheet) {
            try {
                SheetVo first = sheets.get(0);
                sheets = new ArrayList<>();
                sheets.add(first);
            } catch (Exception e) {
                throw new VerifyFailException("参数不正确!");
            }
        }
        for (SheetVo sheet : sheets) {
            List<String> head = sheet.getHeads();
            if (!sheet.isDefaultOrder()) {
                if (head == null || head.size() <= 0) {
                    throw new VerifyFailException("您选择非默认导出并未指定导出顺序,请先指定导出顺序!");
                }
                if (!comments.containsAll(head)) {
                    throw new VerifyFailException("请检查您的参数是否正确!");
                }
            }
        }
        return true;
    }


    public static void main(String[] args) {

        char ch = (char) 94;
        System.out.println(ch);
        ch = (char) 45;
        System.out.println(ch);
        ch = (char) 42;
        System.out.println(ch);

        ch = (char) 126;
        System.out.println(ch);

        ch = (char) 35;
        System.out.println(ch);

        ch = (char) 44;
        System.out.println(ch);

        //模板
        List<String> a = Arrays.asList(new String[]{"a", "b", "c"});

        //实际
        List<String> b = Arrays.asList(new String[]{"a", "c", "b", "b"});


        List<Integer> index = a.stream()
                .map(l -> {
                    return b.indexOf(l);
                })
                .filter(l -> l != -1)
                .collect(Collectors.toList());

        index.stream()
                .forEach(System.out::println);

        long count = b.stream()
                .filter(l -> a.contains(l))
                .count();
        System.out.println(count);


    }

    /**
     * @param columnDesigns 字段信息结构
     * @param name          字段名称
     * @return
     */
    private ColumnDesign resolveFieldType(List<ColumnDesign> columnDesigns, String name) {
        for (ColumnDesign columnDesign : columnDesigns) {
            if (name.equals(columnDesign.getName())) {
                return columnDesign;
            }
        }
        return null;
    }

}
